<?php
require_once ('helper.php');  

if(function_exists("date_default_timezone_set") and function_exists("date_default_timezone_get")) {
  @date_default_timezone_set(@date_default_timezone_get());
}
  
function cleanup_SQL($str) {
  return str_replace(" ,\n", ",\n", $str);
}

function export_header($dbname) {
  $str = "";
  $str .= PHP_EOL;
  $str .= "---------------------------------------------------------------------".PHP_EOL;
  $str .= "-- CUBRID Database Schema".PHP_EOL;
  $str .= "-- ".PHP_EOL;
  $str .= "-- Database: ".$dbname.PHP_EOL;
  $str .= "-- ".PHP_EOL;
  $str .= "-- Created date: ".date("d-m-Y H:i",time()).PHP_EOL;
  $str .= "-- ".PHP_EOL;
  $str .= "-- Project home page: http://code.google.com/p/cubrid-database-schema".PHP_EOL;
  $str .= "---------------------------------------------------------------------".PHP_EOL;
  $str .= PHP_EOL;
  
  return $str;
}

function export_tables($conn, $selected_tables, $delimiter, $export_drops, $use_auto_increment, $filename_seed) {
  $str = "";
  $str .= PHP_EOL;
  $str .= "---------------------------------------------------------------------".PHP_EOL;
  $str .= "-- TABLES".PHP_EOL;
  $str .= "---------------------------------------------------------------------".PHP_EOL;
  $str .= PHP_EOL;

  //$tables = split(",", sort_tables_by_fk($conn, $selected_tables));
  $tables = explode(",", sort_tables_by_fk($conn, $selected_tables));
  
  $str_drop = "";
  $str_def = "";

  foreach($tables as $table) {
    if($filename_seed != "") {
      $filename = "export/db_table_data_".$table."_".$filename_seed.".csv";
      exportCSV($conn, $table, $filename);  
    }
    $str_def .= get_table_definition($conn, $table, $delimiter, $use_auto_increment).PHP_EOL.PHP_EOL;
  } 
  
  foreach($tables as $table) {
    if($export_drops) {
      $str_drop = "DROP TABLE ".$delimiter.$table.$delimiter.";".PHP_EOL.$str_drop;
    }
  } 

  if($export_drops) {
    $str .= $str_drop.PHP_EOL.PHP_EOL;
  }
  $str .= $str_def.PHP_EOL.PHP_EOL;

  return $str;
}

function export_views($conn, $selected_views, $delimiter, $export_drops) {
  $str = "";
  $str .= PHP_EOL;
  $str .= "---------------------------------------------------------------------".PHP_EOL;
  $str .= "-- VIEWS".PHP_EOL;
  $str .= "---------------------------------------------------------------------".PHP_EOL;
  $str .= PHP_EOL;

  $views = split(",", $selected_views);
  foreach($views as $view) {
    if($export_drops) {
      $str .= "DROP VIEW ".$delimiter.$view.$delimiter.";".PHP_EOL;
      $str .= PHP_EOL;
    }
    
    $str .= "CREATE VIEW ".$delimiter.$view.$delimiter." AS ".PHP_EOL;
    $str .= get_view_definition($conn, $view, $delimiter).";".PHP_EOL;
    $str .= PHP_EOL;
  } 
  $str .= PHP_EOL;
  
  return $str;
}

function export_triggers($conn, $selected_triggers, $delimiter, $export_drops) {
  $str = "";
  $str .= PHP_EOL;
  $str .= "---------------------------------------------------------------------".PHP_EOL;
  $str .= "-- TRIGGERS".PHP_EOL;
  $str .= "---------------------------------------------------------------------".PHP_EOL;
  $str .= PHP_EOL;

  $triggers = split(",", $selected_triggers);
  foreach($triggers as $trigger) {
    if($export_drops) {
      $str .= "DROP TRIGGER ".$delimiter.$trigger.$delimiter.";".PHP_EOL;
      $str .= PHP_EOL;
    }
    
    $str .= get_trigger_definition($conn, $trigger, $delimiter).PHP_EOL;
    $str .= PHP_EOL;
  } 
  $str .= PHP_EOL;
  
  return $str;
}

function export_procedures($conn, $selected_procedures, $delimiter, $export_drops) {
  $str = "";
  $str .= PHP_EOL;
  $str .= "---------------------------------------------------------------------".PHP_EOL;
  $str .= "-- PROCEDURES".PHP_EOL;
  $str .= "---------------------------------------------------------------------".PHP_EOL;
  $str .= PHP_EOL;

  $procedures = split(",", $selected_procedures);
  foreach($procedures as $procedure) {
    if($export_drops) {
      $str .= "DROP PROCEDURE ".$delimiter.$procedure.$delimiter.";".PHP_EOL;
      $str .= PHP_EOL;
    }
    
    $str .= get_procedure_definition($conn, $procedure, $delimiter).PHP_EOL;
    $str .= PHP_EOL;
  } 
  $str .= PHP_EOL;
  
  return $str;
}

function export_serials($conn, $selected_serials, $delimiter, $export_drops) {
  $str = "";
  $str .= PHP_EOL;
  $str .= "---------------------------------------------------------------------".PHP_EOL;
  $str .= "-- SERIALS".PHP_EOL;
  $str .= "---------------------------------------------------------------------".PHP_EOL;
  $str .= PHP_EOL;

  $serials = split(",", $selected_serials);
  foreach($serials as $serial) {
    if($export_drops) {
      $str .= "DROP SERIAL ".$delimiter.$serial.$delimiter.";".PHP_EOL;
      $str .= PHP_EOL;
    }
 
    $str .= get_serial_definition($conn, $serial, $delimiter).PHP_EOL;
    $str .= PHP_EOL;
  } 
  $str .= PHP_EOL;
  
  return $str;
}

function get_view_definition($conn, $view, $delimiter) {
  $str = "";
  
  $sql = "select ".$delimiter."vclass_def".$delimiter;
  $sql .= " from ".$delimiter."db_vclass".$delimiter;
  $sql .= " where ".$delimiter."vclass_name".$delimiter." = '".$view."'";

  $result = $conn->query($sql);
  if($result != null) {
    foreach ($result as $row) {  
      $str = $row['vclass_def']; 
    }
  }
  
  return $str;
}

function get_procedure_definition($conn, $procedure, $delimiter) {
  $str = "";
  
  $sql = "select * from ".$delimiter."db_stored_procedure".$delimiter;
  $sql .= " where ".$delimiter."sp_name".$delimiter." = '".$procedure."'";

  $result = $conn->query($sql);
  if($result != null) {
    foreach ($result as $row) {
      if($row['sp_type'] == "PROCEDURE") {
        $str .= "CREATE PROCEDURE ".$delimiter.$procedure.$delimiter;
        $str .= "(".get_procedure_arguments($conn, $procedure, $delimiter).")".PHP_EOL;
        $str .= "AS LANGUAGE ".$row['lang'].PHP_EOL;
        $str .= "NAME '".$row['target']."';".PHP_EOL;
      } else {
        $str .= "CREATE FUNCTION ".$delimiter.$procedure.$delimiter;
        $str .= "(".get_procedure_arguments($conn, $procedure, $delimiter).")".PHP_EOL;
        $str .= "RETURN ".$row['return_type'].PHP_EOL;
        $str .= "AS LANGUAGE ".$row['lang'].PHP_EOL;
        $str .= "NAME '".$row['target']."';".PHP_EOL;
      } 
    }
  }
  
  return $str;
}
  
function get_procedure_arguments($conn, $procedure, $delimiter) {
  $str = "";
  
  $sql = "select * from ".$delimiter."db_stored_procedure_args".$delimiter;
  $sql .= " where ".$delimiter."sp_name".$delimiter." = '".$procedure."'";
  $sql .= " order by ".$delimiter."index_of".$delimiter." asc";

  $result = $conn->query($sql);
  if($result != null) {
    foreach ($result as $row) {
      $str .= "\"".$row['arg_name']."\" ".$row['mode']." ".$row['data_type'].",";
    }
    if(substr($str, strlen($str)-1) == ",") {
      $str = substr($str, 0, strlen($str)-1);
    }
  }
  
  return $str;
}

function get_serial_definition($conn, $serial, $delimiter) {
  $str = "";
  
  $sql = "select * from ".$delimiter."db_serial".$delimiter;
  $sql .= " where ".$delimiter."name".$delimiter." = '".$serial."'";

  $result = $conn->query($sql);
  if($result != null) {
    foreach ($result as $row) {
      $str .= " CREATE SERIAL ".$row['name'].PHP_EOL;
      if($row['min_val'] == "0") {
        $str .= " start with ".$row['started'].PHP_EOL;
      } else {
        $str .= " start with ".$row['min_val'].PHP_EOL;
      }
      $str .= " increment by ".$row['increment_val'].PHP_EOL;
      if($row['min_val'] == "0") {
        $str .= " nominvalue";
      } else {
        $str .= " minvalue ".$row['min_val'];
      }
      $str .= PHP_EOL;
      if($row['max_val'] == "10000000000000000000000000000000000000") {
        $str .= " nomaxvalue";
      } else {
        $str .= " maxvalue ".$row['max_val'];
      }
      $str .= PHP_EOL;
      if($row['cyclic'] == "1") {
        $str .= " cycle".PHP_EOL;
      }
      if($row['cached_num'] == "0") {
        $str .= " nocache";
      } else {
        $str .= " cache ".$row['cached_num'];
      }
      $str .= ";".PHP_EOL;
    }
  }
  
  return $str;
}

function get_trigger_definition($conn, $trigger, $delimiter) {
  $str = "";
  
  $str .= "CREATE TRIGGER ".$delimiter.$trigger.$delimiter.PHP_EOL;
    
  $sql = "select ".$delimiter."db_trig".$delimiter.".*, ".$delimiter."db_trigger".$delimiter.".*";
  $sql .= " from ".$delimiter."db_trig".$delimiter.", ".$delimiter."db_trigger".$delimiter;
  $sql .= " where ".$delimiter."db_trig".$delimiter.".".$delimiter."trigger_name".$delimiter;
  $sql .= " = ";
  $sql .= $delimiter."db_trigger".$delimiter.".".$delimiter."name".$delimiter;
  $sql .= " and ";
  $sql .= $delimiter."db_trigger".$delimiter.".".$delimiter."name".$delimiter;
  $sql .= " = ";
  $sql .= "'".$trigger."'";

  $result = $conn->query($sql);
  if($result != null) {
    foreach ($result as $row) { 
      if($row['status'] == 1) {
        $str .= "STATUS INACTIVE".PHP_EOL;
      }    
      if($row['priority'] != "0.0000000000000000") {
        $str .= "PRIORITY ".$row['priority'].PHP_EOL;
      }
      $timing = "DEFERRED";
      if($row['action_time'] == 1) {
        $timing = "BEFORE";
      }    
      if($row['action_time'] == 2) {
        $timing = "AFTER";
      }
          
      $operation = "";    
      if($row['event'] == 0) {
        $operation = "UPDATE";
      }
      if($row['event'] == 8) {
        $operation = "COMMIT";
      }
      if($row['event'] == 2) {
        $operation = "DELETE";
      }
      if($row['event'] == 5) {
        $operation = "STATEMENT INSERT";
      }
      if($row['event'] == 4) {
        $operation = "INSERT";
      }
      if($row['event'] == 1) {
        $operation = "STATEMENT UPDATE";
      }
      if($row['event'] == 3) {
        $operation = "STATEMENT DELETE";
      }
      if($row['event'] == 9) {
        $operation = "ROLLBACK";
      }
    
      $str .= $timing;
      if($operation != "COMMIT" && $operation != "ROLLBACK") {
        if($operation != "UPDATE" && $operation != "STATEMENT UPDATE") {
          $str .= " ".$operation." ON ".$delimiter.$row['target_class_name'].$delimiter.PHP_EOL;
        } else {
          if($row['target_attr_name'] != "") {
            $str .= " ".$operation." ON ".$delimiter.$row['target_class_name'].$delimiter."(".$delimiter.$row['target_attr_name'].$delimiter.")".PHP_EOL;
          } else {
            $str .= " ".$operation." ON ".$delimiter.$row['target_class_name'].$delimiter.PHP_EOL;
          }
        }
      } else {
        $str .= " ".$operation.PHP_EOL;
      }
      $str .= "EXECUTE";

      if($row['action_type'] == 1) {
        $str .= " ".PHP_EOL.$row['action_definition'].";".PHP_EOL;
      }
      if($row['action_type'] == 2) {
        $str .= " REJECT;".PHP_EOL;
      }
      if($row['action_type'] == 3) {
        $str .= " INVALIDATE TRANSACTION;".PHP_EOL;
      }
      if($row['action_type'] == 4) {
        $str .= " PRINT '".$row['action_definition']."';".PHP_EOL;
      }
    }
  }
  
  return $str;
}

function decode_CUBRID_data_type($conn, $table, $column) {
  $ret = "UNKNOWN";
  $prec = "";  
  $scale = "";  

  $sql = "select a.data_type, a.prec, a.scale from db_attribute a WHERE a.class_name='".$table."'"." AND a.attr_name='".$column."'";
  $result = $conn->query($sql);
  if($result != null) {
    $row = $result->fetch(PDO::FETCH_ASSOC);
    $ret = $row["data_type"];
    if($row["prec"] != 0) {
      $prec = $row["prec"];  
    }
    if($row["scale"] != 0) {
      $scale = $row["scale"];  
    }

    if($ret == "STRING") {
      if($prec != 0) {
        $ret = "VARCHAR";
        $scale = 0;
      }
      if($row["prec"] == 1073741823) {
        $ret = "STRING";
        $prec = 0;
        $scale = 0;
      }
      
      if($prec != 0) {
        if($scale != 0) {
          $ret .= "(".$prec.",".$scale.")";
        } else {
          $ret .= "(".$prec.")";
        }
      } else {
        //
      }
    }
  }
  
  return $ret;
}

function decode_CUBRID_auto_increment($conn, $table, $column, $use_auto_increment) {
  $ret = "";  

  $sql = "select a.name, a.current_val, a.increment_val from db_serial a WHERE a.class_name='".$table."'"." AND a.att_name='".$column."'";
  $result = $conn->query($sql);
  if($result != null) {
    $row = $result->fetch(PDO::FETCH_ASSOC);
    if($row != null) {
      $ret = "AUTO_INCREMENT";

      if($use_auto_increment == "on") {
        $ret .= "(".$row['current_val'].",".$row['increment_val'].")";
      }
    }
  }
  
  return $ret;
}
  
function decode_CUBRID_primary_key($conn, $index_name, $delimiter) {
  $ret = "";  

  $sql = "select key_attr_name from db_index_key WHERE index_name='".$index_name."' order by key_order asc";
  $result = $conn->query($sql);
  if($result != null) {
    while($rows = $result->fetch(PDO::FETCH_ASSOC)) {
      $ret .= $delimiter.$rows['key_attr_name'].$delimiter.",";
    }
    $ret = substr($ret, 0, strlen($ret)-1);
  }

  return $ret;
}

function decode_CUBRID_index($conn, $index_name, $delimiter) {
  $ret = "";  

  $sql = "select key_attr_name, asc_desc from db_index_key WHERE index_name='".$index_name."' order by key_order asc";
  $result = $conn->query($sql);
  if($result != null) {
    while ($rows = $result->fetch(PDO::FETCH_ASSOC)) {
      $ret .= $delimiter.$rows['key_attr_name'].$delimiter." ".$rows['asc_desc'].",";
    }
    
    $ret = substr($ret, 0, strlen($ret)-1);
  }

  return $ret;
}

function get_table_definition($conn, $table, $delimiter, $use_auto_increment) {
  $str = "";
  
  $str .= "CREATE TABLE ".$delimiter.$table.$delimiter."(".PHP_EOL;
  
  $result = $conn->cubrid_schema(PDO::CUBRID_SCH_ATTRIBUTE, $table);
  if($result != null) {
    foreach ($result as $row) {
      $str .= $delimiter.$row["ATTR_NAME"].$delimiter." ";
      $str .= decode_CUBRID_data_type($conn, $table, $row["ATTR_NAME"]);
      if($row["NON_NULL"] == "1") {
        $str .= " NOT NULL ";
      }
      if($row["DEFAULT"] != "") {
        $str .= " DEFAULT ".$row["DEFAULT"]." ";
      }
      $str .= decode_CUBRID_auto_increment($conn, $table, $row["ATTR_NAME"], $use_auto_increment);
      $str .= ",".PHP_EOL;
    }
  }
  
  //primary key
  $result = $conn->cubrid_schema(PDO::CUBRID_SCH_PRIMARY_KEY, $table);
  if($result != null) {
    foreach ($result as $row) {
      $str .= "CONSTRAINT ".$delimiter.$row["KEY_NAME"].$delimiter." ";
      $str .= "PRIMARY KEY (";
      $str .= decode_CUBRID_primary_key($conn, $row["KEY_NAME"], $delimiter);
      $str .= ")";
      $str .= ",".PHP_EOL;
      break;
    }
  }

  //foreign keys
  $result_fk = $conn->cubrid_schema(PDO::CUBRID_SCH_IMPORTED_KEYS, $table);
  if($result_fk != null) {
    foreach ($result_fk as $row_fk ) {
      $str .= "FOREIGN KEY (".$delimiter.$row_fk["FKCOLUMN_NAME"].$delimiter.") ";
      $str .= "REFERENCES ".$delimiter.$row_fk["PKTABLE_NAME"].$delimiter."(".$delimiter.$row_fk["PKCOLUMN_NAME"].$delimiter.")";

      if($row_fk["UPDATE_RULE"] == 0) {
        $str .= " ON UPDATE CASCADE";
      }
      if($row_fk["UPDATE_RULE"] == 1) {
        $str .= " ON UPDATE RESTRICT";
      }
      if($row_fk["UPDATE_RULE"] == 3) {
        $str .= " ON UPDATE SET NULL";
      }
      if($row_fk["DELETE_RULE"] == 0) {
        $str .= " ON DELETE CASCADE";
      }
      if($row_fk["DELETE_RULE"] == 1) {
        $str .= " ON DELETE RESTRICT";
      }
      if($row_fk["DELETE_RULE"] == 3) {
        $str .= " ON DELETE SET NULL";
      }
      $str .= ",".PHP_EOL;
    }
    $str = substr($str, 0, strlen($str)-1);
    $str .= PHP_EOL;
  }
  
  if(substr($str, strlen($str)-2, 1) == ",") {
    $str = substr($str, 0, strlen($str)-2);
  }
  
  $str .= ");";
  $str .= PHP_EOL.PHP_EOL;
  
  //Indexes
  $result = $conn->cubrid_schema(PDO::CUBRID_SCH_CONSTRAINT, $table);
  if($result != null) {
    foreach ($result as $row) {
      if($row["PRIMARY_KEY"] != 1 && $row["KEY_ORDER"] == 1) {
        $str .= "CREATE ";
        if($row["TYPE"] == 0) {
          if($row["ASC_DESC"] == "A") {
            $str .= " UNIQUE INDEX ON ".$delimiter.$table.$delimiter;
          } else {
            $str .= " REVERSE UNIQUE INDEX ON ".$delimiter.$table.$delimiter;
          }
        }
        if($row["TYPE"] == 1) {
          if($row["ASC_DESC"] == "A") {
            $str .= " INDEX ON ".$delimiter.$table.$delimiter;
          } else {
            $str .= " REVERSE INDEX ON ".$delimiter.$table.$delimiter;
          }
        }
        //there is ia bug in CUBRID PDO, values 2 & 3 re not correctly returned
        if($row["TYPE"] == 2) {
          $str .= "REVERSE UNIQUE INDEX ON ".$delimiter.$table.$delimiter;
        }
        if($row["TYPE"] == 3) {
          $str .= "REVERSE INDEX ON ".$delimiter.$table.$delimiter;
        }

        $str .= "(".decode_CUBRID_index($conn, $row["NAME"], $delimiter).")";
        $str .= ";".PHP_EOL;
      }
    }
  }
  
  return $str;
}
  
function export_users($conn, $selected_users, $delimiter, $export_drops) {
  $str = "";
  $str .= PHP_EOL;
  $str .= "-- USERS".PHP_EOL;
  $str .= PHP_EOL;
  $str .= "-- Be aware that USER support is limited in this version.".PHP_EOL;
  $str .= "-- You will have to manually set the passwords/grant the proper authorizations.".PHP_EOL;
  $str .= PHP_EOL;

  $users = split(",",$selected_users);
  foreach($users as $user) {
    if($export_drops) {
      $str .= "DROP USER ".$delimiter.$user.$delimiter.";".PHP_EOL;
      $str .= PHP_EOL;
    }
 
    $str .= get_user_definition($conn, $user, $delimiter).PHP_EOL;
    $str .= PHP_EOL;
    $str .= get_user_authorizations($conn, $user, $delimiter).PHP_EOL;
    $str .= PHP_EOL;
  } 
  $str .= PHP_EOL;
  
  return $str;
}

function get_user_definition($conn, $user, $delimiter) {
  $ret = "";
  $ret .= "CREATE USER ".$delimiter.$user.$delimiter.";".PHP_EOL;
  
  return $ret;
}

function get_table_count($conn, $table, $delimiter) {
  $str = "";
  $sql = "select count(*) from ".$delimiter."$table".$delimiter;
  $result = $conn->query($sql);
  if($result != null) {
    foreach ($result as $row) {
      $str = $row[0];
      break;
    }
  }
  return $str;
}

function get_user_authorizations($conn, $user, $delimiter) {
  $str = "";

  $sql = "select grantor.name as grantor, grantee.name as grantee, class_of.class_name, auth_type, is_grantable";
  $sql .= " from _db_auth";
  $sql .= " where class_of.class_name in (select class_name from db_class where is_system_class='NO')";
  $result = $conn->query($sql);
  if($result != null) {
    foreach ($result as $row) {
      if($row[1] == $user) {
        $str .= "GRANT ".$row[3]." ON ".$delimiter.$row[2].$delimiter." TO ".$delimiter.$row[1].$delimiter;
        if($row[4] == 1) {
          $str .= " WITH GRANT OPTION";
        }
        $str .= ";".PHP_EOL;
      }
    }
  }
  return $str;
}

?>
